Create Staging Tables in Staging Database and Populate the Staging Tables 8
Download the Sql File attached to the
Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.
Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard;
To Load Stg_ReqLocation Table:
Source ReqA:
Source2 Agri Labor Reqs
Table:
Sort Transformation:
Sort1:
Merge Join:
Derived Column:
Code:
InsertDate <add
as new column> GETDATE() database timestamp [DT_DBTIMESTAMP]
UpdateDate <add
as new column> GETDATE() database timestamp [DT_DBTIMESTAMP]
RecordFlag <add as new column> 1 four-byte signed integer [DT_I4]
Personnel Subarea Code Replace 'Personnel Subarea Code' ISNULL([Personnel Subarea Code]) ? "Unknown" : [Personnel Subarea Code] Unicode string [DT_WSTR] 255
Personnel Area Code Replace 'Personnel Area Code' ISNULL([Personnel Area Code]) ? "Unknown" : [Personnel Area Code] Unicode string [DT_WSTR] 255
Address 1 Replace 'Address 1' ISNULL([Address 1]) ? "Unknown" : [Address 1] Unicode string [DT_WSTR] 255
City Replace 'City' ISNULL(City) ? "Unknown" : City Unicode string [DT_WSTR] 255
State/Province Personnel Subarea Abbreviation Replace 'State/Province Personnel Subarea Abbreviation' ISNULL([State/Province Personnel Subarea Abbreviation]) ? "Unknown" : [State/Province Personnel Subarea Abbreviation] Unicode string [DT_WSTR] 255
ZIP/Postal Code Replace 'ZIP/Postal Code' ISNULL([ZIP/Postal Code]) ? "Unknown" : [ZIP/Postal Code] Unicode string [DT_WSTR] 255
Personnel Area Replace 'Personnel Area' ISNULL([Personnel Area]) ? "Unkown" : [Personnel Area] Unicode string [DT_WSTR] 255
Address
2 Replace
'Address 2' ISNULL([Address
2]) ? "Unknown" : [Address 2] Unicode string [DT_WSTR] 255
Conditional Split:
Code:
1 RemoveEmpty LEN([Address 2]) == 0
Derived Column1 :
Code:
Address 2 Replace 'Address 2' "Unknown" Unicode string [DT_WSTR] 255
Union All:
Repeat the same steps to load all the staging tables.
To Load Stg_BusinessCode Table:
Source: Alt_Spend Table and choose all columns
Destination: Stg_BusinessCode:
Mappings:
To Load Stg_ReqDistributor Table:
Source Vw_ReqA:
SQL command Text:
SELECT [ReqA_Requisition ID], [ReqA_Close Comments], [ReqA_Close Reason], [ReqA_Distribution List],
[ReqA_# Cancelled]
FROM [dbo].[vw_ReqA]
Source Timing:
select distinct [Job Posting ID],F8, [Supplier Response],[Selection Process]
FROM [dbo].[Timing]
Source Resumes:
SQL command
Text:
SELECT DISTINCT [Job Posting ID],[Candidate ID], [Security ID],
Candidate, [Candidate First Submit Date], [Candidate Last Interview Date], [Candidate Reject Date], [Candidate Shortlist Date],
[Candidate Status], [Candidate Submit Date], [Quality Rating], [Reason:], [# Shortlisted]
FROM [dbo].[Resumes]
where [Job Posting ID] is not null
Sort:
Sort 1:
Merge Join:
Sort 3:
Derived Column 2 code:
GETDATE()
GETDATE()
ISNULL([Job Posting ID]) ? "Unknown" : [Job
Posting ID]
ISNULL(F8) ? "Unknown" : F8
ISNULL([ReqA_# Cancelled]) ? 0 : [ReqA_# Cancelled]
ISNULL([ReqA_Close Reason]) ? "Unknown" :
[ReqA_Close Reason]
ISNULL([ReqA_Close Comments]) ? "Unknown" :
[ReqA_Close Comments]
ISNULL([ReqA_Distribution List]) ? "Unknown" :
[ReqA_Distribution List]
ISNULL([Security ID]) ? "Unknown" : [Security ID]
ISNULL([Candidate ID]) ? "Unknown" : [Candidate
ID]
ISNULL(Candidate) ? "Unknown" : Candidate
ISNULL([Candidate Status]) ? "Unknown" :
[Candidate Status]
ISNULL([Copy of Candidate Submit Date]) ?
"Unknown" : [Copy of Candidate Submit Date]
ISNULL([Copy of Candidate Reject Date]) ?
"Unknown" : [Copy of Candidate Reject Date]
ISNULL(Reason) ? "Unknown" : Reason
ISNULL([Copy of Candidate Last Interview Date]) ?
"Unknown" : [Copy of Candidate Last Interview Date]
ISNULL([Copy of Candidate First Submit Date]) ? "
Unknown " : [Copy of Candidate First Submit Date]
ISNULL([Copy of Candidate Shortlist Date]) ?
"Unknown" : [Copy of Candidate Shortlist Date]
ISNULL([Copy of # Shortlisted]) ? "0" : [Copy of #
Shortlisted]
ISNULL([Supplier Response]) ? 0 : [Supplier Response]
ISNULL([Quality Rating]) ? "Unknown" : [Quality
Rating]
ISNULL([Selection Process]) ? 0 : [Selection Process]
Conditional Split Transformation:
Derived Column 1:
Union All Transformation:
To Load Stg_Headcount (Full Mth) Table:
SQL Command text:
WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY [Headcount_Work Order ID], [Worker ID_HeadCount]
ORDER BY[Headcount_Month] desc ) AS RankWorkOrderID, *
FROM [dbo].[vw_HeadCount_FullMth] )
SELECT [Headcount_Assignment Length], [Headcount_Diverse Supplier], [Headcount_Period End], [Headcount_Posting Type],
[Headcount_Primary Company Code - Cost Center], [Headcount_Security ID], [Headcount_Tenure]
, [Headcount_Tenure Order], [Headcount_Work Order ID], [Headcount_Worker], [Worker ID_HeadCount],
[Headcount_Worker Pay Type Name], [Headcount_Worker Type]
FROM CTE
where RankWorkOrderID = 1
Derived Column Transformation:
Data Conversion
Union All:
About Merge and Merge Join Transformations
http://www.learnmsbitutorials.net/ssis-merge-and-mergejoin-example.php
Download the Sql File attached to the
Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.
Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard;
To Load Stg_ReqLocation Table:
Source ReqA:
Source2 Agri Labor Reqs
Table:
Sort Transformation:
Sort1:
Merge Join:
Derived Column:
Code:
InsertDate <add
as new column> GETDATE() database timestamp [DT_DBTIMESTAMP]
UpdateDate <add
as new column> GETDATE() database timestamp [DT_DBTIMESTAMP]
RecordFlag <add as new column> 1 four-byte signed integer [DT_I4]
Personnel Subarea Code Replace 'Personnel Subarea Code' ISNULL([Personnel Subarea Code]) ? "Unknown" : [Personnel Subarea Code] Unicode string [DT_WSTR] 255
Personnel Area Code Replace 'Personnel Area Code' ISNULL([Personnel Area Code]) ? "Unknown" : [Personnel Area Code] Unicode string [DT_WSTR] 255
Address 1 Replace 'Address 1' ISNULL([Address 1]) ? "Unknown" : [Address 1] Unicode string [DT_WSTR] 255
City Replace 'City' ISNULL(City) ? "Unknown" : City Unicode string [DT_WSTR] 255
State/Province Personnel Subarea Abbreviation Replace 'State/Province Personnel Subarea Abbreviation' ISNULL([State/Province Personnel Subarea Abbreviation]) ? "Unknown" : [State/Province Personnel Subarea Abbreviation] Unicode string [DT_WSTR] 255
ZIP/Postal Code Replace 'ZIP/Postal Code' ISNULL([ZIP/Postal Code]) ? "Unknown" : [ZIP/Postal Code] Unicode string [DT_WSTR] 255
Personnel Area Replace 'Personnel Area' ISNULL([Personnel Area]) ? "Unkown" : [Personnel Area] Unicode string [DT_WSTR] 255
Address
2 Replace
'Address 2' ISNULL([Address
2]) ? "Unknown" : [Address 2] Unicode string [DT_WSTR] 255
Conditional Split:
Code:
1 RemoveEmpty LEN([Address 2]) == 0
Derived Column1 :
Code:
Address 2 Replace 'Address 2' "Unknown" Unicode string [DT_WSTR] 255
Union All:
Repeat the same steps to load all the staging tables.
To Load Stg_BusinessCode Table:
Source: Alt_Spend Table and choose all columns
Destination: Stg_BusinessCode:
Mappings:
To Load Stg_ReqDistributor Table:
Source Vw_ReqA:
SQL command Text:
SELECT [ReqA_Requisition ID], [ReqA_Close Comments], [ReqA_Close Reason], [ReqA_Distribution List],
[ReqA_# Cancelled]
FROM [dbo].[vw_ReqA]
Source Timing:
select distinct [Job Posting ID],F8, [Supplier Response],[Selection Process]
FROM [dbo].[Timing]
Source Resumes:
SQL command
Text:
SELECT DISTINCT [Job Posting ID],[Candidate ID], [Security ID],
Candidate, [Candidate First Submit Date], [Candidate Last Interview Date], [Candidate Reject Date], [Candidate Shortlist Date],
[Candidate Status], [Candidate Submit Date], [Quality Rating], [Reason:], [# Shortlisted]
FROM [dbo].[Resumes]
where [Job Posting ID] is not null
Sort:
Sort 1:
Merge Join:
Sort 3:
Derived Column 2 code:
GETDATE()
GETDATE()
ISNULL([Job Posting ID]) ? "Unknown" : [Job
Posting ID]
ISNULL(F8) ? "Unknown" : F8
ISNULL([ReqA_# Cancelled]) ? 0 : [ReqA_# Cancelled]
ISNULL([ReqA_Close Reason]) ? "Unknown" :
[ReqA_Close Reason]
ISNULL([ReqA_Close Comments]) ? "Unknown" :
[ReqA_Close Comments]
ISNULL([ReqA_Distribution List]) ? "Unknown" :
[ReqA_Distribution List]
ISNULL([Security ID]) ? "Unknown" : [Security ID]
ISNULL([Candidate ID]) ? "Unknown" : [Candidate
ID]
ISNULL(Candidate) ? "Unknown" : Candidate
ISNULL([Candidate Status]) ? "Unknown" :
[Candidate Status]
ISNULL([Copy of Candidate Submit Date]) ?
"Unknown" : [Copy of Candidate Submit Date]
ISNULL([Copy of Candidate Reject Date]) ?
"Unknown" : [Copy of Candidate Reject Date]
ISNULL(Reason) ? "Unknown" : Reason
ISNULL([Copy of Candidate Last Interview Date]) ?
"Unknown" : [Copy of Candidate Last Interview Date]
ISNULL([Copy of Candidate First Submit Date]) ? "
Unknown " : [Copy of Candidate First Submit Date]
ISNULL([Copy of Candidate Shortlist Date]) ?
"Unknown" : [Copy of Candidate Shortlist Date]
ISNULL([Copy of # Shortlisted]) ? "0" : [Copy of #
Shortlisted]
ISNULL([Supplier Response]) ? 0 : [Supplier Response]
ISNULL([Quality Rating]) ? "Unknown" : [Quality
Rating]
ISNULL([Selection Process]) ? 0 : [Selection Process]
Conditional Split Transformation:
Derived Column 1:
Union All Transformation:
To Load Stg_Headcount (Full Mth) Table:
SQL Command text:
WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY [Headcount_Work Order ID], [Worker ID_HeadCount]
ORDER BY[Headcount_Month] desc ) AS RankWorkOrderID, *
FROM [dbo].[vw_HeadCount_FullMth] )
SELECT [Headcount_Assignment Length], [Headcount_Diverse Supplier], [Headcount_Period End], [Headcount_Posting Type],
[Headcount_Primary Company Code - Cost Center], [Headcount_Security ID], [Headcount_Tenure]
, [Headcount_Tenure Order], [Headcount_Work Order ID], [Headcount_Worker], [Worker ID_HeadCount],
[Headcount_Worker Pay Type Name], [Headcount_Worker Type]
FROM CTE
where RankWorkOrderID = 1
Derived Column Transformation:
Data Conversion
Union All:
About Merge and Merge Join Transformations
http://www.learnmsbitutorials.net/ssis-merge-and-mergejoin-example.php